﻿//------------------------------------------------------------------------------
// 创建标识: Copyright (C) 2008 Socansoft.com 版权所有
// 创建描述: SocanCode代码生成器自动创建于 2008-8-13 17:43:17
//
// 功能描述: 
//
// 修改标识: 
// 修改描述: 
//------------------------------------------------------------------------------

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IDAL;
using DBUtility;
using System.Collections;
using System.Collections.Generic;

namespace SQLServerDAL
{
    /// <summary>
    /// 数据访问类 USERPARAM
    /// </summary>
    public class USERPARAM : IUSERPARAM
    {
        public USERPARAM()
        { }

        #region  成员方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(Model.USERPARAM model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [USERPARAM](");
            strSql.Append("[UP_USERCODE],[UP_PARAMTYPE],[UP_PARAM1],[UP_PARAM2],[UP_PARAM3],[UP_PARAM4],[UP_PARAM5],[UP_PARAM6],[UP_PARAM7],[UP_PARAM8])");
            strSql.Append(" values (");
            strSql.Append("@UP_USERCODE,@UP_PARAMTYPE,@UP_PARAM1,@UP_PARAM2,@UP_PARAM3,@UP_PARAM4,@UP_PARAM5,@UP_PARAM6,@UP_PARAM7,@UP_PARAM8)");
            SqlParameter[] parameters = {
                    new SqlParameter("@UP_USERCODE", SqlDbType.VarChar,20),
                    new SqlParameter("@UP_PARAMTYPE", SqlDbType.VarChar,3),
                    new SqlParameter("@UP_PARAM1", SqlDbType.SmallInt),
                    new SqlParameter("@UP_PARAM2", SqlDbType.SmallInt),
                    new SqlParameter("@UP_PARAM3", SqlDbType.Int),
                    new SqlParameter("@UP_PARAM4", SqlDbType.Int),
                    new SqlParameter("@UP_PARAM5", SqlDbType.DateTime),
                    new SqlParameter("@UP_PARAM6", SqlDbType.DateTime),
                    new SqlParameter("@UP_PARAM7", SqlDbType.VarChar,20),
                    new SqlParameter("@UP_PARAM8", SqlDbType.VarChar,20)
            };
            parameters[0].Value = model.UP_USERCODE;
            parameters[1].Value = model.UP_PARAMTYPE;
            parameters[2].Value = model.UP_PARAM1;
            parameters[3].Value = model.UP_PARAM2;
            parameters[4].Value = model.UP_PARAM3;
            parameters[5].Value = model.UP_PARAM4;

            if (model.UP_PARAM5 != DateTime.MinValue)
                parameters[6].Value = model.UP_PARAM5;
            else
                parameters[6].Value = DBNull.Value;


            if (model.UP_PARAM6 != DateTime.MinValue)
                parameters[7].Value = model.UP_PARAM6;
            else
                parameters[7].Value = DBNull.Value;


            if (model.UP_PARAM7 != null)
                parameters[8].Value = model.UP_PARAM7;
            else
                parameters[8].Value = DBNull.Value;


            if (model.UP_PARAM8 != null)
                parameters[9].Value = model.UP_PARAM8;
            else
                parameters[9].Value = DBNull.Value;


            if (SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0)
                   return model.UP_USERCODE;
            else
                return null;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.USERPARAM model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [USERPARAM] set ");
            strSql.Append("[UP_PARAMTYPE]=@UP_PARAMTYPE,");
            strSql.Append("[UP_PARAM1]=@UP_PARAM1,");
            strSql.Append("[UP_PARAM2]=@UP_PARAM2,");
            strSql.Append("[UP_PARAM3]=@UP_PARAM3,");
            strSql.Append("[UP_PARAM4]=@UP_PARAM4,");
            strSql.Append("[UP_PARAM5]=@UP_PARAM5,");
            strSql.Append("[UP_PARAM6]=@UP_PARAM6,");
            strSql.Append("[UP_PARAM7]=@UP_PARAM7,");
            strSql.Append("[UP_PARAM8]=@UP_PARAM8");
            strSql.Append(" where [UP_USERCODE]=@UP_USERCODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@UP_USERCODE", SqlDbType.VarChar,20),
                    new SqlParameter("@UP_PARAMTYPE", SqlDbType.VarChar,3),
                    new SqlParameter("@UP_PARAM1", SqlDbType.SmallInt),
                    new SqlParameter("@UP_PARAM2", SqlDbType.SmallInt),
                    new SqlParameter("@UP_PARAM3", SqlDbType.Int),
                    new SqlParameter("@UP_PARAM4", SqlDbType.Int),
                    new SqlParameter("@UP_PARAM5", SqlDbType.DateTime),
                    new SqlParameter("@UP_PARAM6", SqlDbType.DateTime),
                    new SqlParameter("@UP_PARAM7", SqlDbType.VarChar,20),
                    new SqlParameter("@UP_PARAM8", SqlDbType.VarChar,20)
            };
            parameters[0].Value = model.UP_USERCODE;
            parameters[1].Value = model.UP_PARAMTYPE;
            parameters[2].Value = model.UP_PARAM1;
            parameters[3].Value = model.UP_PARAM2;
            parameters[4].Value = model.UP_PARAM3;
            parameters[5].Value = model.UP_PARAM4;

            if (model.UP_PARAM5 != DateTime.MinValue)
                parameters[6].Value = model.UP_PARAM5;
            else
                parameters[6].Value = DBNull.Value;


            if (model.UP_PARAM6 != DateTime.MinValue)
                parameters[7].Value = model.UP_PARAM6;
            else
                parameters[7].Value = DBNull.Value;


            if (model.UP_PARAM7 != null)
                parameters[8].Value = model.UP_PARAM7;
            else
                parameters[8].Value = DBNull.Value;


            if (model.UP_PARAM8 != null)
                parameters[9].Value = model.UP_PARAM8;
            else
                parameters[9].Value = DBNull.Value;


            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string UP_USERCODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete [USERPARAM] ");
            strSql.Append(" where [UP_USERCODE]=@UP_USERCODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@UP_USERCODE", SqlDbType.VarChar,20)};
            parameters[0].Value = UP_USERCODE;

            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string UP_USERCODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [USERPARAM]");
            strSql.Append(" where [UP_USERCODE]= @UP_USERCODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@UP_USERCODE", SqlDbType.VarChar,20)};
            parameters[0].Value = UP_USERCODE;
            return SqlHelper.Exists(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.USERPARAM GetModel(string UP_USERCODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [USERPARAM] ");
            strSql.Append(" where [UP_USERCODE]=@UP_USERCODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@UP_USERCODE", SqlDbType.VarChar,20)};
            parameters[0].Value = UP_USERCODE;
            DataSet ds = SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];
                return GetModel(r);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取数据集,建议只在多表联查时使用
        /// </summary>
        public DataSet GetDataSet(string strSql)
        {
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql);
        }

        /// <summary>
        /// 获取泛型数据列表,建议只在多表联查时使用
        /// </summary>
        public IList<Model.USERPARAM> GetList(string strSql)
        {
            return GetList(GetDataSet(strSql));
        }

        /// <summary>
        /// 获取数据集,在单表查询时使用
        /// </summary>
        public DataSet GetDataSet(string strWhat, string strWhere, string strOrderby)
        {
            if (string.IsNullOrEmpty(strWhat))
                strWhat = "*";
            StringBuilder strSql = new StringBuilder("select " + strWhat + " from [USERPARAM]");
            if (!string.IsNullOrEmpty(strWhere))
                strSql.Append(" where " + strWhere);
            if (!string.IsNullOrEmpty(strOrderby))
                strSql.Append(" order by " + strOrderby);
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString());
        }

        /// <summary>
        /// 获取泛型数据列表,在单表查询时使用
        /// </summary>
        public IList<Model.USERPARAM> GetList(string strWhat, string strWhere, string strOrderby)
        {
            return GetList(GetDataSet(strWhat, strWhere, strOrderby));
        }

        /// <summary>
        /// 分页获取泛型数据列表
        /// </summary>
        public IList<Model.USERPARAM> GetList(int pageSize, int pageIndex, string fldSort, bool sort, string strCondition, out int pageCount, out int count)
        {
            string strSql;
            DataSet ds = SqlHelper.PageList(SqlHelper.LocalSqlServer, "[USERPARAM]", null, pageSize, pageIndex, fldSort, sort, strCondition, "UP_USERCODE", false, out pageCount, out count, out strSql);
            return GetList(ds);
        }
        #endregion

        /// <summary>
        /// 由一行数据得到一个实体
        /// </summary>
        private Model.USERPARAM GetModel(DataRow r)
        {
            Model.USERPARAM model = new Model.USERPARAM();
            model.UP_USERCODE = SqlHelper.GetString(r["UP_USERCODE"]);
            model.UP_PARAMTYPE = SqlHelper.GetString(r["UP_PARAMTYPE"]);
            model.UP_PARAM1 = SqlHelper.GetInt(r["UP_PARAM1"]);
            model.UP_PARAM2 = SqlHelper.GetInt(r["UP_PARAM2"]);
            model.UP_PARAM3 = SqlHelper.GetInt(r["UP_PARAM3"]);
            model.UP_PARAM4 = SqlHelper.GetInt(r["UP_PARAM4"]);
            model.UP_PARAM5 = SqlHelper.GetDateTime(r["UP_PARAM5"]);
            model.UP_PARAM6 = SqlHelper.GetDateTime(r["UP_PARAM6"]);
            model.UP_PARAM7 = SqlHelper.GetString(r["UP_PARAM7"]);
            model.UP_PARAM8 = SqlHelper.GetString(r["UP_PARAM8"]);
            return model;
        }

        /// <summary>
        /// 由数据集得到泛型数据列表
        /// </summary>
        private IList<Model.USERPARAM> GetList(DataSet ds)
        {
            List<Model.USERPARAM> l = new List<Model.USERPARAM>();
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                l.Add(GetModel(r));
            }
            return l;
        }
    }
}
